﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Text.RegularExpressions;


namespace HDQApp.Common
{
    public static class SQLHandler
    {
        static SQLHandler()
        {

        }



        /// <summary>
        /// 验证 可能带有声明变量的SQL文本 有无语法错误！
        /// </summary>
        /// <param name="createAlter">Create or alter</param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="content">编辑框编写的内容</param>
        /// <param name="isExec">是否是执行生成操作</param>
        /// <param name="msg">说明性消息内容</param>
        /// <returns></returns>
        public static bool Validate(string createAlter, string procName, string content, bool isExec, out string msg)
        {
            bool bResult = false;
            msg = (isExec ? "分析：" : "执行：") + "命令已成功完成。";
            string newContent = string.Empty;
            string declareSql = GetFrontDeclareSql(content, out newContent);
            string sql = createAlter + " procedure " + procName + "\r\n as \r\n" + declareSql + newContent;
            try
            {
                DbHelperMsSQL.ExecuteSqlTran(new List<string>()
                {
                    "set noexec on;",
                    sql,
                    "set noexec off;"
                });
                bResult = true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return bResult;
        }

        /// <summary>
        /// 执行 生成或修改 存储过程
        /// </summary>
        /// <param name="createAlter">Create or alter</param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="content">编辑框编写的内容</param>
        /// <param name="msg">说明性消息内容</param>
        /// <returns></returns>
        public static bool Exec(string createAlter, string procName, string content, out string msg)
        {
            bool bResult = false;
            msg = "执行：" + "命令已成功完成。";
            string newContent = string.Empty;
            string declareSql = GetFrontDeclareSql(content, out newContent);
            string sql = createAlter + " procedure " + procName + "\r\n as \r\n" + declareSql + newContent;
            int res = 0;
            try
            {
                res = DbHelperMsSQL.ExecuteSql(sql);
                bResult = true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return bResult;
        }



        /// <summary>
        /// 获取拼接 自定义特性的 变量声明部分Sql
        /// </summary>
        /// <param name="content">原文本框内容</param>
        /// <param name="newContent">处理过声明变量的 文本内容</param>
        /// <returns></returns>
        public static string GetFrontDeclareSql(string content, out string newContent)
        {
            newContent = content;
            //获取所有特性名称
            HashSet<string> hsAttr = GetCustomeAttr();

            List<string> lstAttr = new List<string>();
            //查询是否包含 当前特性名称
            foreach (string teXMC in hsAttr)
            {
                //正则匹配 是否 有特性名称存在, \\s* 匹配 0个或多个空白 \\s+ 匹配至少1个空白或多个空白
                if (Regex.IsMatch(content, "\\s*" + teXMC + "\\s+"))
                {
                    lstAttr.Add("'" + teXMC + "'");
                    //匹配到则将 特性名称替换为 @特性名称
                    newContent = Regex.Replace(newContent, "(.+\\s*)(" + teXMC + ")(\\s+.+)", "$1@$2$3");
                }
            }

            string result_sql = string.Empty;
            if (lstAttr.Count > 0)
            {
                string sel_sql = "SELECT a.TeXMC,a.ShuJLX,a.ShuJCD,b.TeZM FROM dbo.sys_Register a LEFT JOIN bom_WuLTX b ON a.TeXMC=b.TeXMC where a.TeXMC in (" + (string.Join(",", lstAttr)) + ")";
                DataTable data = DbHelperMsSQL.QueryTable(sel_sql);

                List<string> lstSql = new List<string>();
                for (int j = 0; j < data.Rows.Count; j++)
                {
                    string TeXMC = data.Rows[j]["TeXMC"].ToString();
                    string ShuJLX = data.Rows[j]["ShuJLX"].ToString();
                    string ShuJCD = (data.Rows[j]["ShuJCD"] == null || string.IsNullOrWhiteSpace(data.Rows[j]["ShuJCD"].ToString())) ? string.Empty : "(" + data.Rows[j]["ShuJCD"].ToString() + ")";
                    string TeZM = data.Rows[j]["TeZM"] == null ? string.Empty : data.Rows[j]["TeZM"].ToString();

                    if (ShuJLX.Equals("decimal", StringComparison.OrdinalIgnoreCase))
                    {
                        ShuJCD = " (" + data.Rows[j]["ShuJCD"].ToString() + ",2)";
                    }

                    string declareSql = "declare @" + TeXMC + " " + ShuJLX + ShuJCD;
                    string selvalSql = "select @" + TeXMC + "=TeXZ from bom_WuLTX where TeZM='" + TeZM + "' and TeXMC='" + TeXMC + "'";
                    lstSql.Add(declareSql);
                    lstSql.Add(selvalSql);
                }
                result_sql = string.Join(";\r\n", lstSql);
                result_sql += ";\r\n";
            }
            return result_sql;
        }



        #region 关键字设置
        /// <summary>
        /// 蓝色(#0000FF)
        /// </summary>
        public static HashSet<string> GetKeyWordsByBlue()
        {
            HashSet<string> lstKWordByBlue = new HashSet<string>()
            {
               "ADD","ALTER","AS","ASC","AUTHORIZATION",
               "BACKUP","BEGIN","BREAK","BROWSE","BULK","BY","BIT","BINARY",
               "CASCADE","CASE","CHECK","CHECKPOINT","CLOSE","CLUSTERED","COLLATE",
               "COLUMN","COMMIT","COMPUTE","CONSTRAINT","CONTAINS","CONTAINSTABLE","CONTINUE","CREATE","CURRENT","CURRENT_DATE","CURRENT_TIME","CURSOR","CHAR",
               "DATABASE","DBCC","DEALLOCATE","DECLARE","DEFAULT","DELETE","DENY","DESC","DISK","DISTINCT","DISTRIBUTED","DOUBLE","DROP","DUMP","DECIMAL","DATATIME",
               "ELSE","END","ERRLVL","ESCAPE","EXCEPT","EXEC","EXECUTE","EXIT",
               "FETCH","FILE","FILLFACTOR","FOR","FOREIGN","FREETEXT","FREETEXTTABLE","FROM","FULL","FUNCTION","FLOAT",
               "GOTO","GRANT","GROUP",
               "HAVING","HOLDLOCK",
               "IDENTITY","IDENTITY_INSERT","IDENTITYCOL","IF","INDEX","INSERT","INTERSECT","INTO","INT","IMAGE",
               "KEY","KILL",
               "LINENO","LOAD",
                "MONEY",
               "NATIONAL","NOCHECK","NONCLUSTERED","NUMERIC","NCHAR","NVARCHAR","NTEXT",
               "OF","OFF","OFFSETS","ON","OPEN","OPENDATASOURCE","OPENQUERY","OPENROWSET","OPENXML","OPTION","ORDER","OVER",
               "PERCENT","PLAN","PRECISION","PRIMARY","PRINT","PROC","PROCEDURE","PUBLIC",
               "RAISERROR","READ","READTEXT","RECONFIGURE","REFERENCES","REPLICATION","RESTORE","RESTRICT","RETURN","REVOKE","ROLLBACK","ROWCOUNT","ROWGUIDCOL","RULE","REAL",
               "SAVE","SCHEMA","SELECT","SET","SETUSER","SHUTDOWN","STATISTICS","SMALLINT","SMALLDATETIME",
               "TABLE","TEXTSIZE","THEN","TO","TOP","TRAN","TRANSACTION","TRIGGER","TRUNCATE","TSEQUAL","TINYINT","TEXT","TIMESTAMP",
               "UNION","UNIQUE","UPDATE","UPDATETEXT","USE","UNIQUEIDENTIFIER",
               "VALUES","VARYING","VIEW","VARCHAR","VARBINARY",
               "WAITFOR","WHEN","WHERE","WHILE","WITH","WRITETEXT"
            };
            return lstKWordByBlue;
        }

        /// <summary>
        /// 牡丹红(#FF00FF)
        /// </summary>
        public static HashSet<string> GetKeyWordsByPeony()
        {
            HashSet<string> lstKWordByPeony = new HashSet<string>()
            {
               "ASCII","CHAR","LOWER","UPPER","STR","LTRIM","RTRIM","SUBSTRING","CHARINDEX","PATINDEX",
              "REPLACE","STUFF","DAY","MONTH","YEAR","DATEADD","DATEDIFF",
               "DATEPART","GETDATE","AVG","MIN","SUM","ABS","MAX","CEILING","ROUND","RAND","COUNT",
               "CONVERT","CAST",
               "@@FETCH_STATUS"
            };
            return lstKWordByPeony;
        }

        /// <summary>
        /// 灰色(#C0C0C0)
        /// </summary>
        public static HashSet<string> GetKeyWordsByGray()
        {
            HashSet<string> lstKWordByGray = new HashSet<string>()
            {
                "INNER","JOIN","AND","NULL","IS","OR","CROSS","LEFT","RIGHT",
            };
            return lstKWordByGray;
        }
        #endregion




        /// <summary>
        /// 获取所有 自定义特性的 名称
        /// </summary>
        /// <returns></returns>
        public static HashSet<string> GetCustomeAttr()
        {
            HashSet<string> lstAttr = new HashSet<string>();
            string sel_sql = "SELECT DISTINCT TeXMC AS TeXMC  FROM dbo.sys_Register";
            lstAttr = new HashSet<string>(DbHelperMsSQL.QueryTable(sel_sql).GetFirstCol<string>());     //用哈希表返回了指定SQL命令所得到的第一字段列表
            return lstAttr;
        }
    }
}
